package com.ildepinc.persistence.abstracts;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Properties;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.ildepinc.persistence.concretes.JdbcConnection;
import com.ildepinc.persistence.concretes.JdbcDriver;
import com.ildepinc.persistence.concretes.PropertiesReader;
import com.ildepinc.persistence.concretes.daos.DaoCatalog;
import com.ildepinc.persistence.concretes.daos.DaoCountry;
import com.ildepinc.persistence.concretes.daos.DaoLanguage;
import com.ildepinc.persistence.concretes.daos.DaoLocale;
import com.ildepinc.persistence.concretes.models.LanguageSequence;
import com.ildepinc.persistence.constants.PersistenceConstants;

import com.ildepinc.persistence.interfaces.PersistenceLayerBehaviour;

public abstract class PersistenceLayer implements PersistenceLayerBehaviour {

  private Logger logger = LogManager.getLogger(PersistenceLayer.class);
  private Connection connection;
  private Statement statement;
  private JdbcConnection jdbcConnection;
  private HashMap<String, DaoTableGeneric> daoTableGenericMap;
  private HashMap<String, DaoTableGenericExpirable> daoTableGenericExpirableMap;
  private HashMap<String, DaoTableAuditable> daoTableAuditableMap;
  private HashMap<String, DaoTableAuditableExpirable> daoTableAuditableExpirableMap;
  private HashMap<String, SequenceGeneric> sequenceGenericMap;
  private Properties persistenceProperties;
  private Properties environmentProperties;

  @Override
  public Boolean startLayer() {
    Boolean result = Boolean.valueOf(true);
    result = this.setup();
    if (result) {
      try {
        this.statement = this.connection.createStatement();
      } catch (Exception e) {
        result = Boolean.valueOf(false);
        logger.error("Problem with statement creatio ", e);
        e.printStackTrace();
      }
      try {
        checkAndCreeateSchema(this.environmentProperties.getProperty("schema.name"));
      } catch (Exception e) {
        result = Boolean.valueOf(false);
        logger.error("Problem with schema " + this.environmentProperties.getProperty("schema.name"), e);
        e.printStackTrace();
      }
      if (result) {
        result = this.setupDaos();
      }
      if (result) {
        result = this.setupSequences();
      }
    }
    if (result) {
      logger.info("PersistenceLayer started!");
    }
    return result;
  }

  @Override
  public Boolean stopLayer() {
    Boolean result = Boolean.valueOf(true);
    try {
      if ((this.statement != null) && (!this.statement.isClosed())) {
        this.statement.close();
        logger.info("Statement closed");
      }
      if ((this.connection != null) && (!this.connection.isClosed())) {
        this.connection.close();
        logger.info("Connection closed");
      }
    } catch (SQLException e) {
      result = Boolean.valueOf(false);
      logger.error("Unable to stop persistence layer ..", e);
      e.printStackTrace();
    }
    if (result) {
      logger.info("PersistenceLayer stopped!");
    }
    return result;
  }

  @Override
  public void checkAndCreateTable(String schema, String table, String sqlDdl) throws Exception {
    Boolean createifnone = Boolean.valueOf(this.environmentProperties.getProperty("tables.createifnone"));
    if (this.jdbcConnection.doesTableExists(schema, table)) {
      logger.info("Table " + table + " exists");
    } else {
      logger.info("Table " + schema + "." + table + " does NOT exists");
      if (createifnone) {
        logger.debug("sqlDdl => " + sqlDdl);
        this.statement.execute(sqlDdl);
        logger.info("Table " + schema + "." + table + " created");
      }
    }
  }

  @Override
  public void checkAndCreateSequence(String sequenceName, String sqlDdl) throws Exception {
    Boolean hasSequence = Boolean.valueOf(this.environmentProperties.getProperty("database.hassequences"));
    if (hasSequence) {
      Boolean createifnone = Boolean.valueOf(this.environmentProperties.getProperty("sequences.createifnone"));
      String sqlToCheck = this.environmentProperties.getProperty("sequences.sqltocheckexists");
      sqlToCheck = sqlToCheck + "'" + sequenceName.toUpperCase() + "'";
      logger.debug("sqlToCheck => " + sqlToCheck);
      this.statement = this.connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet rs = this.statement.executeQuery(sqlToCheck);
      rs.first();
      Integer quanti = rs.getInt(1);
      if ((quanti == 0) && createifnone) {
        logger.debug("sqlDdl => " + sqlDdl);
        this.statement.execute(sqlDdl);
        logger.info("Sequence " + sequenceName + " created");
      }
    }
  }

  @Override
  public void checkAndCreeateSchema(String schema) throws Exception {
    String sqlSchemaDdl = "CREATE SCHEMA " + schema;
    Boolean createifnone = Boolean.valueOf(this.environmentProperties.getProperty("schema.createifnone"));
    if (this.jdbcConnection.doesSchemaExists(schema)) {
      logger.info("Schema " + schema + " exists");
    } else {
      logger.info("Schema " + schema + " does NOT exists");
      if (createifnone) {
        logger.debug("sqlSchemaDdl => " + sqlSchemaDdl);
        this.statement.execute(sqlSchemaDdl);
        logger.info("Schema " + schema + " created");
      }
    }
  }

  @Override
  public JdbcDriver createJdbcDriver(Properties persistenceProperty) throws Exception {
    JdbcDriver jdbcDriver = new JdbcDriver(this.persistenceProperties.getProperty("conf.path"),
        this.persistenceProperties.getProperty("environment"), PersistenceConstants.ENV_PROPERTY_FILE_NAME,
        this.persistenceProperties.getProperty("db.path"));
    return jdbcDriver;
  }

  @Override
  public Boolean setup() {
    Boolean result = Boolean.valueOf(true);
    JdbcDriver jdbcDriver = null;
    this.daoTableAuditableExpirableMap = new HashMap<String, DaoTableAuditableExpirable>();
    this.daoTableAuditableMap = new HashMap<String, DaoTableAuditable>();
    this.daoTableGenericExpirableMap = new HashMap<String, DaoTableGenericExpirable>();
    this.daoTableGenericMap = new HashMap<String, DaoTableGeneric>();
    this.sequenceGenericMap = new HashMap<String, SequenceGeneric>();
    try {
      this.persistenceProperties = PropertiesReader.getPersistenceProperties();
      jdbcDriver = this.createJdbcDriver(this.persistenceProperties);
      this.environmentProperties = jdbcDriver.getProperties();
      logger.info("Jdbc Full Url -> " + jdbcDriver.composeFullUrl());
      this.jdbcConnection = new JdbcConnection(jdbcDriver);
      this.connection = this.jdbcConnection.doConnect();
      logger.info("Connection successfull!");
    } catch (Exception e) {
      result = Boolean.valueOf(false);
      logger.error("Unable to setup persistence layer", e);
      e.printStackTrace();
    }
    return result;
  }

  @Override
  public Boolean setupDaos() {
    Boolean result = Boolean.valueOf(true);
    DaoCatalog daoCatalog = new DaoCatalog(this.jdbcConnection, this.environmentProperties.getProperty("schema.name"));
    DaoLanguage daoLanguage = new DaoLanguage(this.jdbcConnection,
        this.environmentProperties.getProperty("schema.name"));
    DaoCountry daoCountry = new DaoCountry(this.jdbcConnection, this.environmentProperties.getProperty("schema.name"));
    DaoLocale daoLocale = new DaoLocale(this.jdbcConnection, this.environmentProperties.getProperty("schema.name"));
    this.daoTableGenericMap.put("DaoCatalog", daoCatalog);
    this.daoTableAuditableMap.put("DaoLanguage", daoLanguage);
    this.daoTableAuditableExpirableMap.put("DaoCountry", daoCountry);
    this.daoTableGenericExpirableMap.put("DaoLocale", daoLocale);
    if (result) {
      result = setupDaoTableGeneric(result);
    }
    if (result) {
      result = setupDaoTableGenericExpirable(result);
    }
    if (result) {
      result = setupDaoTableAuditable(result);
    }
    if (result) {
      result = setupDaoTableAuditableExpirable(result);
    }
    return result;
  }

  @Override
  public Boolean setupSequences() {
    Boolean result = Boolean.valueOf(true);
    LanguageSequence languageSequence = new LanguageSequence(this.environmentProperties.getProperty("schema.name"));
    this.sequenceGenericMap.put("LanguageSequence", languageSequence);
    if (result) {
      result = setupSequenceGeneric(result);
    }
    return result;
  }

  @Override
  public Boolean setupSequenceGeneric(Boolean result) {
    for (SequenceGeneric entry : this.sequenceGenericMap.values()) {
      try {
        checkAndCreateSequence(
            entry.getSequenceName(),
            entry.getSqlCreate());
      } catch (Exception e) {
        result = Boolean.valueOf(false);
        logger.error("Problem with sequence " + entry.getSchema() + "." + entry.getSequenceName(), e);
        e.printStackTrace();
      }
    }
    return result;
  }

  @Override
  public Boolean setupDaoTableAuditableExpirable(Boolean result) {
    for (DaoTableAuditableExpirable entry : this.daoTableAuditableExpirableMap.values()) {
      try {
        checkAndCreateTable(
            entry.getTable().getSchema(),
            entry.getTable().getTableName(),
            entry.getTable().getSqlCreateTable());
      } catch (Exception e) {
        result = Boolean.valueOf(false);
        logger.error("Problem with table " + entry.getTable().getSchema() + "." + entry.getTable().getTableName(), e);
        e.printStackTrace();
      }
    }
    return result;
  }

  @Override
  public Boolean setupDaoTableAuditable(Boolean result) {
    for (DaoTableAuditable entry : this.daoTableAuditableMap.values()) {
      try {
        checkAndCreateTable(
            entry.getTable().getSchema(),
            entry.getTable().getTableName(),
            entry.getTable().getSqlCreateTable());
      } catch (Exception e) {
        result = Boolean.valueOf(false);
        logger.error("Problem with table " + entry.getTable().getSchema() + "." + entry.getTable().getTableName(), e);
        e.printStackTrace();
      }
    }
    return result;
  }

  @Override
  public Boolean setupDaoTableGenericExpirable(Boolean result) {
    for (DaoTableGenericExpirable entry : this.daoTableGenericExpirableMap.values()) {
      try {
        checkAndCreateTable(
            entry.getTable().getSchema(),
            entry.getTable().getTableName(),
            entry.getTable().getSqlCreateTable());
      } catch (Exception e) {
        result = Boolean.valueOf(false);
        logger.error(
            "Problem with table " + entry.getTable().getSchema() + "." + entry.getTable().getTableName(), e);
        e.printStackTrace();
      }
    }
    return result;
  }

  @Override
  public Boolean setupDaoTableGeneric(Boolean result) {
    for (DaoTableGeneric entry : this.daoTableGenericMap.values()) {
      try {
        checkAndCreateTable(
            entry.getTable().getSchema(),
            entry.getTable().getTableName(),
            entry.getTable().getSqlCreateTable());
      } catch (Exception e) {
        result = Boolean.valueOf(false);
        logger.error(
            "Problem with table " + entry.getTable().getSchema() + "." + entry.getTable().getTableName(), e);
        e.printStackTrace();
      }
    }
    return result;
  }

  @Override
  public void clear() {
    this.statement = null;
    this.connection = null;
    this.jdbcConnection = null;
    this.daoTableGenericMap = null;
    this.daoTableGenericExpirableMap = null;
    this.daoTableAuditableMap = null;
    this.daoTableAuditableExpirableMap = null;
    this.sequenceGenericMap = null;
    this.persistenceProperties = null;
    this.environmentProperties = null;
  }

  @Override
  public Logger getLogger() {
    return logger;
  }

  @Override
  public void setLogger(Logger logger) {
    this.logger = logger;
  }

  @Override
  public Connection getConnection() {
    return connection;
  }

  @Override
  public void setConnection(Connection connection) {
    this.connection = connection;
  }

  @Override
  public Statement getStatement() {
    return statement;
  }

  @Override
  public void setStatement(Statement statement) {
    this.statement = statement;
  }

  @Override
  public JdbcConnection getJdbcConnection() {
    return jdbcConnection;
  }

  @Override
  public void setJdbcConnection(JdbcConnection jdbcConnection) {
    this.jdbcConnection = jdbcConnection;
  }

  @Override
  public HashMap<String, DaoTableGeneric> getDaoTableGenericMap() {
    return daoTableGenericMap;
  }

  @Override
  public void setDaoTableGenericMap(HashMap<String, DaoTableGeneric> daoTableGenericMap) {
    this.daoTableGenericMap = daoTableGenericMap;
  }

  @Override
  public HashMap<String, DaoTableGenericExpirable> getDaoTableGenericExpirableMap() {
    return daoTableGenericExpirableMap;
  }

  @Override
  public void setDaoTableGenericExpirableMap(HashMap<String, DaoTableGenericExpirable> daoTableGenericExpirableMap) {
    this.daoTableGenericExpirableMap = daoTableGenericExpirableMap;
  }

  @Override
  public HashMap<String, DaoTableAuditable> getDaoTableAuditableMap() {
    return daoTableAuditableMap;
  }

  @Override
  public void setDaoTableAuditableMap(HashMap<String, DaoTableAuditable> daoTableAuditableMap) {
    this.daoTableAuditableMap = daoTableAuditableMap;
  }

  @Override
  public HashMap<String, DaoTableAuditableExpirable> getDaoTableAuditableExpirableMap() {
    return daoTableAuditableExpirableMap;
  }

  @Override
  public void setDaoTableAuditableExpirableMap(
      HashMap<String, DaoTableAuditableExpirable> daoTableAuditableExpirableMap) {
    this.daoTableAuditableExpirableMap = daoTableAuditableExpirableMap;
  }

  @Override
  public Properties getPersistenceProperties() {
    return persistenceProperties;
  }

  @Override
  public void setPersistenceProperties(Properties persistenceProperties) {
    this.persistenceProperties = persistenceProperties;
  }

  @Override
  public Properties getEnvironmentProperties() {
    return environmentProperties;
  }

  @Override
  public void setEnvironmentProperties(Properties environmentProperties) {
    this.environmentProperties = environmentProperties;
  }

  @Override
  public HashMap<String, SequenceGeneric> getSequenceGenericMap() {
    return sequenceGenericMap;
  }

  @Override
  public void setSequenceGenericMap(HashMap<String, SequenceGeneric> sequenceGenericMap) {
    this.sequenceGenericMap = sequenceGenericMap;
  }

}
